package com.kingdee.eas.cp.bc.export;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

import com.alibaba.fastjson.JSONObject;
import com.kingdee.bos.BOSException;
import com.kingdee.bos.eventbus.event.action.KDActionEvent;
import com.kingdee.bos.webframework.exception.WafException;
import com.kingdee.bos.webframework.dynamic.handler.AbstractWebEventHandler;
import com.kingdee.bos.eventbus.event.action.IActionEventHandler;
import com.kingdee.bos.metadata.entity.EntityViewInfo;
import com.kingdee.bos.metadata.entity.FilterInfo;
import com.kingdee.bos.metadata.entity.FilterItemInfo;
import com.kingdee.bos.metadata.entity.SelectorItemCollection;
import com.kingdee.bos.metadata.entity.SelectorItemInfo;
import com.kingdee.bos.metadata.query.util.CompareType;
import com.kingdee.eas.cp.bc.BizAccountBillAccountEntryCollection;
import com.kingdee.eas.cp.bc.BizAccountBillAccountEntryFactory;
import com.kingdee.eas.cp.bc.BizAccountBillAccountEntryInfo;
import com.kingdee.eas.cp.bc.BizAccountOutBillCollection;
import com.kingdee.eas.cp.bc.BizAccountOutBillEntry;
import com.kingdee.eas.cp.bc.BizAccountOutBillEntryCollection;
import com.kingdee.eas.cp.bc.BizAccountOutBillEntryFactory;
import com.kingdee.eas.cp.bc.BizAccountOutBillEntryInfo;
import com.kingdee.eas.cp.bc.BizAccountOutBillFactory;
import com.kingdee.eas.cp.bc.BizAccountOutBillInfo;
import com.kingdee.eas.cp.bc.DailyLoanBillAccountEntry;
import com.kingdee.eas.cp.bc.DailyLoanBillAccountEntryCollection;
import com.kingdee.eas.cp.bc.DailyLoanBillAccountEntryFactory;
import com.kingdee.eas.cp.bc.DailyLoanBillAccountEntryInfo;
import com.kingdee.eas.cp.bc.DailyLoanBillCollection;
import com.kingdee.eas.cp.bc.DailyLoanBillFactory;
import com.kingdee.eas.cp.bc.DailyLoanBillInfo;
import com.kingdee.eas.cp.bc.TravelAccountBill;
import com.kingdee.eas.cp.bc.TravelAccountBillAccountEntry;
import com.kingdee.eas.cp.bc.TravelAccountBillAccountEntryCollection;
import com.kingdee.eas.cp.bc.TravelAccountBillAccountEntryFactory;
import com.kingdee.eas.cp.bc.TravelAccountBillAccountEntryInfo;

public class ExcelExportHandler extends AbstractWebEventHandler implements IActionEventHandler {
	public void onAction(KDActionEvent event) throws WafException,BOSException {
		String data=event.getReqeustContext().getHttpServletRequest().getParameter("datas");
		JSONObject ids=JSONObject.parseObject(data);
		Set set=new HashSet();
		for(String key:ids.keySet()){
			set.add(key);
		}
		
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		String[] excelHeader = {}; //表头
		//type 类型  1:借款单  2:费用报销  3:对公费用报销  4:差旅费报销  5:对公费用报销单明细  6:对公费用报销单汇总
		//type 1~5都是统一的格式   6是自定义格式  所以需要区分开
		String type=event.getReqeustContext().getHttpServletRequest().getParameter("type");
		if(type.equals("6")){
			try {
				getOutCollectExport(event.getResponseContext().getHttpServletResponse(),set);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}else{
			if(type.equals("1")){
				excelHeader = new String[]{"支付方式","收款人","开户银行","开户地","银行账号","币别","汇率","原币金额","本位币金额"}; 
				getLoanGatheringData(set, list);
			}else if(type.equals("2")){
				excelHeader = new String[]{"支付方式","收款人","开户银行","开户地","银行账号","身份证号码","币别","汇率","原币金额","本位币金额"}; 
				getCostGatheringData(set, list);
			}else if(type.equals("3")){
				excelHeader=new String[]{"支付方式","收款人类型","收款人","收款银行","收款人账号","开户地","身份证号码","报销金额"};
				getOutGatheringData(set, list);
			}else if(type.equals("4")){
				excelHeader =new String[]{"支付方式","收款人","开户银行","开户地","银行账号","币别","汇率","原币金额","本位币金额"}; 
				getTravelGatheringData(set, list);
			}else if(type.equals("5")){
				excelHeader =new String[]{"单据编码","预算项目","核定报销金额","申请人","申请时间","收款人类型","收款人","收款用户","银行账号","支付方式"}; 
				getOutdetailData(set, list);
			}
			try {
				exportExcel(event.getResponseContext().getHttpServletResponse(),list,excelHeader);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	private void getOutCollectExport(HttpServletResponse response,Set set)throws Exception{
		EntityViewInfo view=new EntityViewInfo();
		FilterInfo filter=new FilterInfo();
		SelectorItemCollection sic=new SelectorItemCollection();
		filter.getFilterItems().add(new FilterItemInfo("bill.id",set,CompareType.INCLUDE));
		view.setFilter(filter);
		sic.add(new SelectorItemInfo("*"));
		sic.add(new SelectorItemInfo("project.name"));
		sic.add(new SelectorItemInfo("project.number"));
		view.setSelector(sic);
		
		String SBGZ="ZC";
		String XX="XX";
		int num=0;
		Map<String, BigDecimal> map=new HashMap<String, BigDecimal>();
		BigDecimal sbgzMoney=new BigDecimal(0);
		BizAccountOutBillEntryCollection collection = BizAccountOutBillEntryFactory.getRemoteInstance().getBizAccountOutBillEntryCollection(view);
		for(int i=0;i<collection.size();i++){
			BizAccountOutBillEntryInfo info=collection.get(i);
			if(info.getProject()!=null){
				if(info.getProject().getNumber().startsWith(SBGZ)){
					if(info.getAmountApproved()!=null){
						sbgzMoney=sbgzMoney.add(info.getAmountApproved()).setScale(2,BigDecimal.ROUND_DOWN);
					}
					num++;
				}else if(info.getProject().getNumber().startsWith(XX)){
					if(map.containsKey(info.getProject().getName())){
						BigDecimal money=map.get(info.getProject().getName());
						if(info.getAmountApproved()!=null){
							money=money.add(info.getAmountApproved()).setScale(2,BigDecimal.ROUND_DOWN);
							map.put(info.getProject().getName(), money);
						}
					}else{
						map.put(info.getProject().getName(),info.getAmountApproved()!=null?info.getAmountApproved().setScale(2,BigDecimal.ROUND_DOWN):new BigDecimal(0));
					}
					num++;
				}
			}
		}
		
		HSSFWorkbook wb = new HSSFWorkbook();  
        HSSFSheet sheet = wb.createSheet("导出");  
        
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        
        HSSFCellStyle styleRight = wb.createCellStyle();  
        styleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        
        int rowNum=0;
        CellRangeAddress callRangeAddress1 = new CellRangeAddress(rowNum,rowNum,0,5);
        sheet.addMergedRegion(callRangeAddress1);
        HSSFRow row = sheet.createRow(rowNum);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("上海交通大学医学院附属第九人民医院");
        cell.setCellStyle(style);
        
        rowNum++;
        CellRangeAddress callRangeAddress2 = new CellRangeAddress(rowNum,rowNum,0,5);
        sheet.addMergedRegion(callRangeAddress2);
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("对外报销单");
        cell.setCellStyle(style);
        
        rowNum++;
        CellRangeAddress callRangeAddressd = new CellRangeAddress(rowNum,rowNum,0,5);
        sheet.addMergedRegion(callRangeAddressd);
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("附明细"+num+"条");
        cell.setCellStyle(styleRight);
        
        rowNum++;
        CellRangeAddress callRangeAddress3 = new CellRangeAddress(rowNum,rowNum,0,3);
        sheet.addMergedRegion(callRangeAddress3);
        CellRangeAddress callRangeAddress4 = new CellRangeAddress(rowNum,rowNum,4,5);
        sheet.addMergedRegion(callRangeAddress4);
        
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("内容");
        cell.setCellStyle(style);
        cell = row.createCell(4);
        cell.setCellValue("金额");
        cell.setCellStyle(style);
        
        rowNum++;
    	CellRangeAddress callRangeAddress10 = new CellRangeAddress(rowNum,rowNum,0,3);
        sheet.addMergedRegion(callRangeAddress10);
        CellRangeAddress callRangeAddress11 = new CellRangeAddress(rowNum,rowNum,4,5);
        sheet.addMergedRegion(callRangeAddress11);
        
        BigDecimal subtotalMoney=new BigDecimal(0);
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("设备购置");
        cell.setCellStyle(style);
        row.createCell(4).setCellValue(Double.valueOf(sbgzMoney.toString()));
        subtotalMoney=subtotalMoney.add(sbgzMoney);
        
        for(String key:map.keySet()){
        	rowNum++;
        	CellRangeAddress callRangeAddress5 = new CellRangeAddress(rowNum,rowNum,0,3);
            sheet.addMergedRegion(callRangeAddress5);
            CellRangeAddress callRangeAddress6 = new CellRangeAddress(rowNum,rowNum,4,5);
            sheet.addMergedRegion(callRangeAddress6);
            
            row = sheet.createRow(rowNum);
            cell = row.createCell(0);
            cell.setCellValue(key);
            cell.setCellStyle(style);
            
            row.createCell(4).setCellValue(Double.valueOf(map.get(key).toString()));
            subtotalMoney=subtotalMoney.add(map.get(key));
        }
        
        rowNum++;
        CellRangeAddress callRangeAddress7 = new CellRangeAddress(rowNum,rowNum,0,3);
        sheet.addMergedRegion(callRangeAddress7);
        CellRangeAddress callRangeAddress8 = new CellRangeAddress(rowNum,rowNum,4,5);
        sheet.addMergedRegion(callRangeAddress8);
        
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("合计（小写）");
        cell.setCellStyle(style);
        row.createCell(4).setCellValue(Double.valueOf(subtotalMoney.toString()));
        
        rowNum++;
        row = sheet.createRow(rowNum);
        row.createCell(0).setCellValue("主管院领导");
        row.createCell(1).setCellValue("分管院领导");
        row.createCell(2).setCellValue("联签部门负责人");
        row.createCell(3).setCellValue("经办/报销部门负责人");
        row.createCell(4).setCellValue("财务处审核");
        row.createCell(5).setCellValue("经办人/报销人"); 
        
        response.setContentType("application/vnd.ms-excel");  
        response.setHeader("Content-disposition", "attachment;filename=ExportData.xls");  
        OutputStream ouputStream = response.getOutputStream();  
        wb.write(ouputStream);  
        ouputStream.flush();  
        ouputStream.close();  
	}
	
	/**
	 * 获取对公费用报销单明细数据
	 * @param set  选择导出行ID集合
	 * @param list 导出的数据源
	 * @throws BOSException
	 */
	private void getOutdetailData(Set set, List<Map<String, Object>> list)
			throws BOSException {
		SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd");
		EntityViewInfo view=new EntityViewInfo();
		FilterInfo filter=new FilterInfo();
		SelectorItemCollection sic=new SelectorItemCollection();
		filter.getFilterItems().add(new FilterItemInfo("bill.id",set,CompareType.INCLUDE));
		view.setFilter(filter);
		sic.add(new SelectorItemInfo("*"));
		sic.add(new SelectorItemInfo("bill.*"));
		sic.add(new SelectorItemInfo("project.*"));
		sic.add(new SelectorItemInfo("bill.applier.*"));
		sic.add(new SelectorItemInfo("bill.payerType.*"));
		sic.add(new SelectorItemInfo("bill.payMode.*"));
		
		view.setSelector(sic);
		//excelHeader =new String[]{"单据编码","预算项目","核定报销金额","申请人","申请时间","收款人类型","收款人","收款银行","银行账号","支付方式"}; 
		BizAccountOutBillEntryCollection collection = BizAccountOutBillEntryFactory.getRemoteInstance().getBizAccountOutBillEntryCollection(view);
		for(int i=0;i<collection.size();i++){
			BizAccountOutBillEntryInfo info=collection.get(i);
			Map<String,Object> map=new HashMap<String, Object>();
			map.put("1", info.getBill().getNumber());
			map.put("2", info.getProject()==null?"":info.getProject().getName());
			map.put("3", info.getAmountApproved()==null?new BigDecimal(0):info.getAmountApproved().setScale(2,BigDecimal.ROUND_DOWN));
			map.put("4", info.getBill().getApplier().getName());
			map.put("5", sf.format(info.getBill().getBizReqDate()));
			map.put("6", info.getBill().getPayerType().getAlias());
			map.put("7", info.getBill().getPayerName());
			map.put("8", info.getBill().getPayerBank());
			map.put("9", info.getBill().getPayerAccount());
			map.put("10", info.getBill().getPayMode().getName());
			list.add(map);
		}
	}

	/**
	 * 获取差旅费报销单收款人信息
	 * @param set  选择导出行ID集合
	 * @param list 导出的数据源
	 * @throws BOSException
	 */
	private void getTravelGatheringData(Set set, List<Map<String, Object>> list)
			throws BOSException {
		EntityViewInfo view=new EntityViewInfo();
		FilterInfo filter=new FilterInfo();
		SelectorItemCollection sic=new SelectorItemCollection();
		filter.getFilterItems().add(new FilterItemInfo("bill.id",set,CompareType.INCLUDE));
		view.setFilter(filter);
		sic.add(new SelectorItemInfo("*"));
		sic.add(new SelectorItemInfo("payMode.name"));
		sic.add(new SelectorItemInfo("currencyType.name"));
		view.setSelector(sic);
		
		TravelAccountBillAccountEntryCollection collection = TravelAccountBillAccountEntryFactory.getRemoteInstance().getTravelAccountBillAccountEntryCollection(view);
		for(int i=0;i<collection.size();i++){
			TravelAccountBillAccountEntryInfo info=collection.get(i);
			Map<String,Object> map=new HashMap<String, Object>();
			map.put("1", info.getPayMode().getName());
			map.put("2", info.getPayerName());
			map.put("3", info.getPayerBank());
			map.put("4", info.getAccountOpenArea());
			map.put("5", info.getPayerAccount());
			map.put("6", info.getCurrencyType().getName());
			map.put("7", info.getExchangeRate()==null?new BigDecimal(0):info.getExchangeRate().setScale(4,BigDecimal.ROUND_DOWN));
			map.put("8", info.getAmountOri()==null?new BigDecimal(0):info.getAmountOri().setScale(2,BigDecimal.ROUND_DOWN));
			map.put("9", info.getAmount()==null?new BigDecimal(0):info.getAmount().setScale(2,BigDecimal.ROUND_DOWN));
			list.add(map);
		}
	}

	/**
	 * 获取对公费用报销单收款人数据
	 * @param set  选择导出行ID集合
	 * @param list 导出的数据源
	 * @throws BOSException
	 */
	private void getOutGatheringData(Set set, List<Map<String, Object>> list)
			throws BOSException {
		EntityViewInfo view=new EntityViewInfo();
		FilterInfo filter=new FilterInfo();
		SelectorItemCollection sic=new SelectorItemCollection();
		filter.getFilterItems().add(new FilterItemInfo("id",set,CompareType.INCLUDE));
		view.setFilter(filter);
		sic.add(new SelectorItemInfo("*"));
		sic.add(new SelectorItemInfo("payMode.name"));
		sic.add(new SelectorItemInfo("openArea.name"));
		view.setSelector(sic);
		BizAccountOutBillCollection collection = BizAccountOutBillFactory.getRemoteInstance().getBizAccountOutBillCollection(view);
		for(int i=0;i<collection.size();i++){
			BizAccountOutBillInfo info=collection.get(i);
			Map<String,Object> map=new HashMap<String, Object>();
			map.put("1", info.getPayMode().getName());
			map.put("2", info.getPayerType().getAlias());
			map.put("3", info.getPayerName());
			map.put("4", info.getPayerBank());
			map.put("5", info.getPayerAccount());
			if(null!=info.getOpenArea()){
				map.put("6", info.getOpenArea().getName());
			}
			map.put("7", info.getString("identityCard"));
			map.put("8", info.getAmount()==null?new BigDecimal(0):info.getAmount().setScale(2,BigDecimal.ROUND_DOWN));
			list.add(map);
		}
	}

	/**
	 * 获取费用报销单收款人数据
	 * @param set  选择导出行ID集合
	 * @param list 导出的数据源
	 * @throws BOSException
	 */
	private void getCostGatheringData(Set set, List<Map<String, Object>> list)
			throws BOSException {
		EntityViewInfo view=new EntityViewInfo();
		FilterInfo filter=new FilterInfo();
		SelectorItemCollection sic=new SelectorItemCollection();
		filter.getFilterItems().add(new FilterItemInfo("bill.id",set,CompareType.INCLUDE));
		view.setFilter(filter);
		sic.add(new SelectorItemInfo("*"));
		sic.add(new SelectorItemInfo("payMode.name"));
		sic.add(new SelectorItemInfo("currencyType.name"));
		view.setSelector(sic);
		
		BizAccountBillAccountEntryCollection collection = BizAccountBillAccountEntryFactory.getRemoteInstance().getBizAccountBillAccountEntryCollection(view);
		for(int i=0;i<collection.size();i++){
			BizAccountBillAccountEntryInfo info=collection.get(i);
			Map<String,Object> map=new HashMap<String, Object>();
			map.put("1", info.getPayMode()==null?"":info.getPayMode().getName());
			map.put("2", info.getPayerName());
			map.put("3", info.getPayerBank());
			map.put("4", info.getAccountOpenArea());
			map.put("5", info.getPayerAccount());
			map.put("6", info.getString("identityCard"));
			map.put("7", info.getCurrencyType().getName());
			map.put("8", info.getExchangeRate()==null?new BigDecimal(0):info.getExchangeRate().setScale(4,BigDecimal.ROUND_DOWN));
			map.put("9", info.getAmountOri()==null?new BigDecimal(0):info.getAmountOri().setScale(2,BigDecimal.ROUND_DOWN));
			map.put("10", info.getAmount()==null?new BigDecimal(0):info.getAmount().setScale(2,BigDecimal.ROUND_DOWN));
			list.add(map);
		}
	}

	/**
	 * 获取借款单收款人数据
	 * @param set  选择导出行ID集合
	 * @param list 导出的数据源
	 * @throws BOSException
	 */
	private void getLoanGatheringData(Set set, List<Map<String, Object>> list)
			throws BOSException {
		EntityViewInfo view=new EntityViewInfo();
		FilterInfo filter=new FilterInfo();
		SelectorItemCollection sic=new SelectorItemCollection();
		filter.getFilterItems().add(new FilterItemInfo("bill.id",set,CompareType.INCLUDE));
		view.setFilter(filter);
		sic.add(new SelectorItemInfo("*"));
		sic.add(new SelectorItemInfo("payMode.name"));
		sic.add(new SelectorItemInfo("currencyType.name"));
		view.setSelector(sic);
		DailyLoanBillAccountEntryCollection collection = DailyLoanBillAccountEntryFactory.getRemoteInstance().getDailyLoanBillAccountEntryCollection(view);
		for(int i=0;i<collection.size();i++){
			DailyLoanBillAccountEntryInfo info=collection.get(i);
			Map<String,Object> map=new HashMap<String, Object>();
			map.put("1", info.getPayMode().getName());
			map.put("2", info.getPayerName());
			map.put("3", info.getPayerBank());
			map.put("4", info.getAccountOpenArea());
			map.put("5", info.getPayerAccount());
			map.put("6", info.getCurrencyType().getName());
			map.put("7", info.getExchangeRate()==null?new BigDecimal(0):info.getExchangeRate().setScale(4,BigDecimal.ROUND_DOWN));
			map.put("8", info.getAmountOri()==null?new BigDecimal(0):info.getAmountOri().setScale(2,BigDecimal.ROUND_DOWN));
			map.put("9", info.getAmount()==null?new BigDecimal(0):info.getAmount().setScale(2,BigDecimal.ROUND_DOWN));
			list.add(map);
		}
	}
	
	/**
	 * excel 导出
	 * @param response
	 * @param list 行数据源
	 * @param excelHeader  表头
	 * @throws Exception
	 */
	public void exportExcel(HttpServletResponse response,List<Map<String,Object>> list,String[] excelHeader)   
	    throws Exception {  
	          
	        HSSFWorkbook wb = new HSSFWorkbook();  
	        HSSFSheet sheet = wb.createSheet("导出");  
	        HSSFRow row = sheet.createRow((int) 0);  
	        HSSFCellStyle style = wb.createCellStyle();  
	        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	        style.setFillForegroundColor(HSSFColor.YELLOW.index);
	        
	        for (int i = 0; i < excelHeader.length; i++) {  
	            HSSFCell cell = row.createCell(i);  
	            cell.setCellValue(excelHeader[i]);  
	            cell.setCellStyle(style);  
	            sheet.autoSizeColumn(i);  
	        }  
	  
	        for (int i = 0; i < list.size(); i++) {  
	            row = sheet.createRow(i + 1);  
	            Map<String , Object> map = list.get(i);  
	            
	            for (int j = 0; j < excelHeader.length; j++) { 
	            	Object value=map.get((j+1)+"");
	            	if(value instanceof BigDecimal){
	            		row.createCell(j).setCellValue(Double.valueOf(String.valueOf(value))); 
	            	}else{
	            		if(null==value){
	            			value="";
	            		}
	            		row.createCell(j).setCellValue(String.valueOf(value)); 
	            	}
	            	 
	            }
	        }  
	        
	        response.setContentType("application/vnd.ms-excel");  
	        response.setHeader("Content-disposition", "attachment;filename=ExportData.xls");  
	        OutputStream ouputStream = response.getOutputStream();  
	        wb.write(ouputStream);  
	        ouputStream.flush();  
	        ouputStream.close();  
	   }

}